CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetApplicationChart`(
    IN p_emp_code VARCHAR(100),
    IN p_started_on TIMESTAMP,
    IN p_ended_on TIMESTAMP,
    IN p_show VARCHAR(100),
    IN p_device VARCHAR(100),
    IN p_chartType VARCHAR(100),
    IN p_type VARCHAR(100)
)
BEGIN
	-- Check if p_started_on and p_ended_on are the same
    IF (p_started_on IS NOT NULL AND p_ended_on IS NOT NULL AND p_started_on = p_ended_on) THEN
        -- Add one day to p_ended_on
        SET p_ended_on = DATE_ADD(p_ended_on, INTERVAL 1 DAY);
    END IF;
	
    IF p_type = 'Applications' THEN
        -- Calculate the total number of records grouped by date
        SELECT 
            COUNT(*) AS ApplicationCount,
            CAST(started_on AS DATE) AS StartedOn, 
            '' AS ApplicationName
        FROM 
            running_application_details rad 
        WHERE 
            (p_started_on IS NULL OR rad.started_on >= p_started_on) 
            AND (p_ended_on IS NULL OR rad.started_on <= p_ended_on)
            AND (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'))
        GROUP BY 
            CAST(started_on AS DATE);
    ELSEIF p_type = 'Application(Top 10)' THEN
        -- Calculate the total number of records grouped by application name
        SELECT 
            COUNT(application_name) AS ApplicationCount,
            application_name AS ApplicationName,
            null AS StartedOn
        FROM 
            running_application_details rad 
        WHERE 
            (p_started_on IS NULL OR rad.started_on >= p_started_on)
            AND (p_ended_on IS NULL OR rad.started_on <= p_ended_on)
            AND (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', p_emp_code, '%'))
        GROUP BY 
            application_name
        ORDER BY 
            ApplicationCount DESC 
        LIMIT 10;
    END IF;
END